

alter     function dbo.f_msmoneygetpriorinfo(
  @moneyid integer,
  @subjectid integer,
  @nyear integer,
  @nmonth integer,
  @filid integer
)
returns 
  @temp_table table (
    nbalyear integer ,
    nbalmonth integer ,
    bmbala decimal(18,2),
    bysumcredit decimal(18,2),
    bysumdebit decimal(18,2),
    tmsumcredit decimal(18,2),
    tmsumdebit decimal(18,2),
    tmsum decimal(18,2),
    fbmbala decimal(18,2),
    fbysumcredit decimal(18,2),
    fbysumdebit decimal(18,2),
    ftmsumcredit decimal(18,2),
    ftmsumdebit decimal(18,2),
    ftmsum decimal(18,2)
  )
as
begin
  declare @nbalyear integer
  declare @nbalmonth integer
  declare @maxyear integer
  declare @maxmonth integer
  declare @newfilid integer
  declare @bmbala decimal(18,2) 
  declare @bysumcredit decimal(18,2)
  declare @bysumdebit decimal(18,2)
  declare @tmsumcredit decimal(18,2)
  declare @tmsumdebit decimal(18,2)
  declare @tmsum decimal(18,2)
  declare @fbmbala decimal(18,2)
  declare @fbysumcredit decimal(18,2)
  declare @fbysumdebit decimal(18,2)
  declare @ftmsumcredit decimal(18,2)
  declare @ftmsumdebit decimal(18,2)
  declare @ftmsum decimal(18,2)

  declare @sum_table table (
    nbalyear integer,
    nbalmonth integer,
    filid integer,
    bmbala decimal(18,2),
    bysumcredit decimal(18,2),
    bysumdebit decimal(18,2),
    tmsumcredit decimal(18,2),
    tmsumdebit decimal(18,2),
    tmsum decimal(18,2),
    fbmbala decimal(18,2),
    fbysumcredit decimal(18,2),
    fbysumdebit decimal(18,2),
    ftmsumcredit decimal(18,2),
    ftmsumdebit decimal(18,2),
    ftmsum decimal(18,2)
  )

  declare cur_filiale cursor local forward_only static read_only
    for select fi_filid from td_filiale where fi_filid = @filid or @filid = -99

  open cur_filiale 
  fetch next from  cur_filiale into @newfilid
  while @@fetch_status = 0 
  begin   
    set @nbalyear     = 0
    set @nbalmonth    = 0
    set @bmbala       = 0.00
    set @bysumcredit  = 0.00
    set @bysumdebit   = 0.00
    set @tmsumcredit  = 0.00
    set @tmsumdebit   = 0.00
    set @tmsum        = 0.00
    set @fbmbala      = 0.00
    set @fbysumcredit = 0.00
    set @fbysumdebit  = 0.00
    set @ftmsumcredit = 0.00
    set @ftmsumdebit  = 0.00
    set @ftmsum       = 0.00

    select top 1 @nbalyear     = ms.nyear,
                 @nbalmonth    = ms.nmonth,
                 @bmbala       = ms.bmbala,
                 @bysumcredit  = ms.bysumcredit,
                 @bysumdebit   = ms.bysumdebit,
                 @tmsumcredit  = ms.tmsumcredit,
                 @tmsumdebit   = ms.tmsumdebit,
                 @tmsum        = ms.tmsum,
                 @fbmbala      = ms.fbmbala,
                 @fbysumcredit = ms.fbysumcredit,
                 @fbysumdebit  = ms.fbysumdebit,
                 @ftmsumcredit = ms.ftmsumcredit,
                 @ftmsumdebit  = ms.ftmsumdebit,
                 @ftmsum       = ms.ftmsum
    from tft_mssub ms
    inner join tfd_subject s on s.subjectid = ms.subjectid
    where (@moneyid is null or s.moneyid = @moneyid) and s.subnode = 1 and s.subjectid = @subjectid
      and ((ms.nyear < @nyear) or (ms.nyear = @nyear and ms.nmonth < @nmonth))
      and ms.filid = @newfilid
    order by ms.nyear desc, ms.nmonth desc

    if (@bmbala       is null) set @bmbala       = 0.00
    if (@bysumcredit  is null) set @bysumcredit  = 0.00
    if (@bysumdebit   is null) set @bysumdebit   = 0.00
    if (@tmsumcredit  is null) set @tmsumcredit  = 0.00
    if (@tmsumdebit   is null) set @tmsumdebit   = 0.00
    if (@tmsum        is null) set @tmsum        = 0.00
    if (@fbmbala      is null) set @fbmbala      = 0.00
    if (@fbysumcredit is null) set @fbysumcredit = 0.00
    if (@fbysumdebit  is null) set @fbysumdebit  = 0.00
    if (@ftmsumcredit is null) set @ftmsumcredit = 0.00
    if (@ftmsumdebit  is null) set @ftmsumdebit  = 0.00
    if (@ftmsum       is null) set @ftmsum       = 0.00

    if (@nbalyear <> @nyear)
    begin
      set @bysumcredit  = 0.00
      set @bysumdebit   = 0.00
      set @fbysumcredit = 0.00
      set @fbysumdebit  = 0.00
    end

    insert into @sum_table values(@nbalyear, @nbalmonth, @newfilid, @bmbala,  
                                  @bysumcredit, @bysumdebit, @tmsumcredit,
                                  @tmsumdebit, @tmsum, @fbmbala, @fbysumcredit,  
                                  @fbysumdebit, @ftmsumcredit, @ftmsumdebit, @ftmsum)
    
    update @sum_table set bysumcredit = 0.00, bysumdebit = 0.00,
                          fbysumcredit = 0.00, fbysumdebit = 0.00 
      where nbalyear <> @nyear and filid = @newfilid
    fetch next from cur_filiale into @newfilid
  end
  close cur_filiale
  deallocate  cur_filiale

  select @maxyear  = max(nbalyear)  from @sum_table
  select @maxmonth = max(nbalmonth) from @sum_table where nbalyear = @maxyear

  declare cur_tmp  cursor local forward_only static read_only
    for select nbalyear, nbalmonth, sum(bmbala), sum(bysumcredit), sum(bysumdebit),
      sum(tmsumcredit), sum(tmsumdebit), sum(tmsum), sum(fbmbala), sum(fbysumcredit),
      sum(fbysumdebit), sum(ftmsumcredit), sum(ftmsumdebit), sum(ftmsum)
    from @sum_table group by nbalyear, nbalmonth order by nbalyear desc, nbalmonth desc
  open cur_tmp 
  fetch next from cur_tmp into @nbalyear, @nbalmonth, @bmbala,  
                               @bysumcredit, @bysumdebit, @tmsumcredit,
                               @tmsumdebit, @tmsum, @fbmbala, @fbysumcredit,  
                               @fbysumdebit, @ftmsumcredit, @ftmsumdebit, @ftmsum
  while @@fetch_status = 0 
  begin   
    if (@nbalyear = @maxyear and @nbalmonth = @maxmonth)  
    begin
      insert into @temp_table values(@nbalyear, @nbalmonth, @bmbala, @bysumcredit,
                                   @bysumdebit, @tmsumcredit, @tmsumdebit,
                                   @tmsum, @fbmbala, @fbysumcredit, @fbysumdebit,
                                   @ftmsumcredit, @ftmsumdebit, @ftmsum)
    end
    if (@nbalyear <> @maxyear or @nbalmonth <> @maxmonth)
    begin
        update @temp_table set bmbala = bmbala + @bmbala + @tmsumdebit - @tmsumcredit,
                               fbmbala = fbmbala + @fbmbala + @ftmsumdebit - @ftmsumcredit
        where nbalyear = @maxyear and nbalmonth = @maxmonth
    end
    fetch next from cur_tmp into @nbalyear, @nbalmonth, @bmbala,
                                  @bysumcredit, @bysumdebit, @tmsumcredit,
                                  @tmsumdebit, @tmsum, @fbmbala, @fbysumcredit,
                                  @fbysumdebit, @ftmsumcredit, @ftmsumdebit, @ftmsum
  end
  close cur_tmp
  deallocate  cur_tmp
  return

end






